create or replace package ac_utils is

  -- Author  : LEV ERUSALIMSKIY
  -- Created : 02/12/2011 11:02:48 PM
  -- Purpose :

  G_AUTH_SCHEMES     constant varchar2(12) := 'AUTH_SCHEMES';
  G_VALIDATIONS      constant varchar2(11) := 'VALIDATIONS';
  G_PROCESS          constant varchar2(7)  := 'PROCESS';
  G_REGION           constant varchar2(6)  := 'REGION';
  G_COMPUTATION      constant varchar2(11) := 'COMPUTATION';
  G_REPORT           constant varchar2(6)  := 'REPORT';
  G_REPORT_PRINT_PARAMETER constant varchar2(22) := 'REPORT_PRINT_PARAMETER';
  G_REPORT_COLUMN    constant varchar2(13) := 'REPORT_COLUMN';
  G_PAGE             constant varchar2(4)  := 'PAGE';
  G_PAGE_ITEMS       constant varchar2(10) := 'PAGE_ITEMS';
  G_APPLICATION_ITEM constant varchar2(16) := 'APPLICATION_ITEM';
  G_APPLICATION      constant varchar2(11) := 'APPLICATION';
  G_BUTTON           constant varchar2(11) := 'BUTTON';
  G_BRANCH           constant varchar2(11) := 'BRANCH';
  G_LOVS             constant varchar2(11) := 'LOV';
  G_LOV_ENTRIES      constant varchar2(30) := 'LOV_ENTRIES';
  G_DYN_ACTIONS      constant varchar2(11) := 'DYN_ACTIONS';
  G_DA_ACTIONS       constant varchar2(11) := 'DA_ACTIONS';
  G_AUTHORIZATION    constant varchar2(30) := 'APPLICATION_AUTHORIZATION';
  G_INTERACTIVE_REPORT constant varchar2(30) := 'INTERACTIVE_REPORT';
  G_INTERACTIVE_REPORT_COLS constant varchar2(30) := 'INTERACTIVE_REPORT_COLS';
  G_PAGE_PROCESS            constant varchar2(30) := 'PAGE_PROCESS';
  G_SUBST_STRINGS    constant varchar2(30)  := 'APPLICATION_SUBSTITUTIONS';
  G_NAV_BAR          constant varchar2(30)  := 'NAVIGATION_BAR';
  G_SHORTCUTS        constant varchar2(30)  := 'APP_SHORTCUTS';
  G_APP_THEMES       constant varchar2(30)  := 'APP_THEMES';
  G_APP_TEMPLATES    constant varchar2(30)  := 'APP_TEMPLATES';
  G_TEMPL_BREADCRUMB constant varchar2(30)  := 'APP_TEMPL_BREADCRUMB';
  G_TEMPL_BUTTON     constant varchar2(30)  := 'APP_TEMPL_BUTTON';
  G_TEMPL_CALENDAR   constant varchar2(30)  := 'APP_TEMPL_CALENDAR';
  G_TEMPL_LABEL      constant varchar2(30)  := 'APP_TEMPL_LABEL';
  G_TEMPL_LIST       constant varchar2(30)  := 'APP_TEMPL_LIST';
  G_TEMPL_PAGE       constant varchar2(30)  := 'APP_TEMPL_PAGE';
  G_TEMPL_LOV        constant varchar2(30)  := 'APP_TEMPL_LOV';
  G_TEMPL_REGION     constant varchar2(30)  := 'APP_TEMPL_REGION';
  G_TEMPL_REPORT     constant varchar2(30)  := 'APP_TEMPL_REPORT';
  G_APP_LISTS        constant varchar2(30)  := 'APP_LISTS';
  G_APP_LISTS_ENTRIES constant varchar2(30) := 'APP_LISTS_ENTRIES';
  G_APP_BREADCRUMBS  constant varchar2(30)  := 'APP_BREADCRUMBS';
  G_APP_BC_ENTRIES   constant varchar2(30)  := 'APP_BREADCRUMBS_ENTRIES';
  G_CANCELED         constant varchar2(30)  := 'CANCELED';
  G_IR_COMP          constant varchar2(30)  := 'APP_IR_COMP';
  G_ALERT_WARNING    constant varchar2(30)  := 'ALERT_WARNING';

  G_CR   constant varchar2(1) := chr(13);
  G_LF   constant varchar2(1) := chr(10);

  G_CRLF constant varchar2(2) := G_CR || G_LF;

  TYPE SIMP_COMP_REC_TYP IS RECORD (
      object_name   varchar2(4000),
      u_name        varchar2(4000),
      u_value_1     varchar2(4000),
      u_value_2     varchar2(4000),
      src_view_name varchar2(4000)
  );
  
  TYPE SIMP_COMP_CUR_TYP IS REF CURSOR RETURN SIMP_COMP_REC_TYP; 

  function ins_result_msg(p_result_id number,  pi_parent_id number, p_msg varchar2, p_sort number := 1,
                         p_isbranch boolean  := true, p_object_type varchar2:= null,
                         p_help_text varchar2:= null
                         ) return number;

  function in1NotIn2(p_msg varchar2, p_app_id_1 varchar2, p_app_id_2 varchar2) return varchar2;

  function getParamName(p_src varchar2, p_max_len number) return varchar2;

  function hs(pc clob) return varchar2 deterministic;

  procedure simp_comp(p_result_id number,   p_parent_id number, 
                      p_obj_type  varchar2, p_cv        SIMP_COMP_CUR_TYP);
                      
  PROCEDURE compl_comp(p_result_id number, pi_parent_id number, 
                       p_sort      number, p_help_text  varchar2,
                       p_cv        in out  SYS_REFCURSOR);                      

end ac_utils;
/

create or replace package body ac_utils is

gc_hash varchar2(100) := utl_raw.cast_to_varchar2(dbms_crypto.Hash(to_clob('***'),3));

procedure create_session(pi_app_id number, pi_user_name varchar2, pi_session varchar2) is
l_cgivar_name owa.vc_arr;
l_cgivar_val owa.vc_arr;
--
l_workspace_id number;
l_application_id number := pi_app_id; /* change this to your application ID */
l_user varchar2(30) := pi_user_name;

BEGIN

-- set up cgi environment
htp.init;
l_cgivar_name(1) := 'REQUEST_PROTOCOL';
l_cgivar_val(1) := 'HTTP';
owa.init_cgi_env(num_params => 1,param_name => l_cgivar_name,param_val => l_cgivar_val);

-- find workspace ID of application
for c1 in (select workspace_id from apex_applications where application_id = l_application_id) loop
l_workspace_id := c1.workspace_id;
end loop;

-- set security group ID, session ID, app ID globals
wwv_flow_api.set_security_group_id(l_workspace_id);
apex_application.g_instance := wwv_flow_custom_auth.get_next_session_id;
apex_application.g_flow_id := l_application_id;

-- create authenticated session for l_user
wwv_flow_custom_auth_std.post_login(
p_uname => l_user,
p_session_id => apex_application.g_instance,
p_flow_page => apex_application.g_flow_id||':'||1);

--apex_util.set_session_state('P168_ID','434');

/*
wwv_flow.show (
p_flow_id => apex_application.g_flow_id,
p_instance => apex_application.g_instance,
P_FLOW_STEP_ID => '168'
);
*/

wwv_flow_api.set_security_group_id(l_workspace_id);


--apex_util.set_session_state('P168_ID','435');

 apex_util.set_session_state('P1_APP_ID1','100');
 apex_util.set_session_state('P1_APP_ID2','101');

 --init_res_collection;


END;

function hs(pc clob) return varchar2
   deterministic
   is
 v_res varchar2(100);
begin
  if pc is null then
    v_res := gc_hash;
  elsif dbms_lob.getlength(pc) = 0 then
    v_res := gc_hash;
  else
    v_res := utl_raw.cast_to_varchar2(dbms_crypto.Hash(pc, 3));
  end if;
  return v_res;
end hs;

 function ins_result_msg(p_result_id number,  pi_parent_id number, p_msg varchar2, p_sort number := 1,
                         p_isbranch boolean  := true, p_object_type varchar2:= null,
                         p_help_text varchar2:= null
                         ) return number is
   v_res_id number;
   v_is_branch varchar2(1);
   begin
     if nvl(p_isbranch,true) then
      v_is_branch := 'Y';
     else
      v_is_branch := 'N';
     end if;

     insert into ac_results
         (result_id, parent_id, param_name, sort,  is_branch, object_type,     help_text, is_src)
     values
         (p_result_id,  pi_parent_id, p_msg, p_sort, v_is_branch, p_object_type, p_help_text, 'N')
     returning id into v_res_id;

     return v_res_id;
 end  ins_result_msg;

 function in1NotIn2(p_msg varchar2, p_app_id_1 varchar2, p_app_id_2 varchar2) return varchar2 is
 begin
  return  p_msg ||' in '||p_app_id_1||' not in '||p_app_id_2;
 end  in1NotIn2;

 function getParamName(p_src varchar2, p_max_len number) return varchar2 as
 v_res varchar2(4000) := p_src;
 begin
    if length(v_res) > p_max_len then
     v_res := substr(v_res, 1, p_max_len)||'...';
    end if;

    return v_res;
 end getParamName;

 function getColID (p_col_name varchar2, p_desctab in out DBMS_SQL.DESC_TAB3, p_colcnt number ) return varchar2 is
 v_colID    number;
 begin
     v_colID := null;
     FOR i IN 1 .. p_colcnt LOOP
      if  upper(p_desctab(i).col_name) = upper(p_col_name) then
       v_colID := i; 
       exit;
      end if;
     END LOOP;
     
     return v_colid;
 end getColID;

 function getColVal(p_curid NUMBER, p_col_name varchar2, p_desctab in out DBMS_SQL.DESC_TAB3, p_colcnt number ) return varchar2 is
 v_colID    number;
 v_colVal   varchar2(4000);
 begin
     v_colID := getColID (p_col_name, p_desctab, p_colcnt);
     
     if v_colid is not null then
      DBMS_SQL.COLUMN_VALUE(p_curid, v_colID, v_colVal  );
     end if;
     
     return v_colVal;
 end getColVal;
    
 function getColValClob(p_curid NUMBER, p_col_name varchar2, p_desctab in out DBMS_SQL.DESC_TAB3, p_colcnt number ) return clob is
 v_colID    number;
 v_colVal   CLOB;
 begin
     v_colID := getColID (p_col_name, p_desctab, p_colcnt);
     
     if v_colid is not null then
      DBMS_SQL.COLUMN_VALUE(p_curid, v_colID, v_colVal  );
     end if;
     
     return v_colVal;
 end getColValClob;

 procedure simp_comp(p_result_id number,   p_parent_id number, 
                     p_obj_type  varchar2, p_cv        SIMP_COMP_CUR_TYP
                    ) is
 v_parent_id_1    number;
 v_object_name    varchar2(4000) := '*#*#*#';
 
 v_res            SIMP_COMP_REC_TYP;
 begin
  loop
    fetch p_cv INTO v_res;
    EXIT WHEN p_cv%NOTFOUND;
    
    if (v_object_name != v_res.object_name) then
       v_parent_id_1 := ac_utils.ins_result_msg(p_result_id, p_parent_id, v_res.object_name, 1, true, p_obj_type );
       v_object_name := v_res.object_name;
    end if;

   insert into ac_results
    (result_id,   parent_id,     param_name,   param_val_1,     param_val_2,
     sort , help_text, object_type, 
     is_branch, is_src)
   values
    (p_result_id, v_parent_id_1, v_res.u_name, v_res.u_value_1, v_res.u_value_2,
     1,    v_res.src_view_name||'.'||v_res.u_name, null,
     'N', 'N' );    
  end loop;

 end  simp_comp;

PROCEDURE compl_comp(p_result_id number, pi_parent_id number, 
                     p_sort      number, p_help_text  varchar2,
                     p_cv        in out  SYS_REFCURSOR) IS

  v_curid         NUMBER;
  v_desctab       DBMS_SQL.DESC_TAB3;
  v_colcnt        NUMBER;
  --
  v_ac_rec        AC_RESULTS%ROWTYPE;
  --
  v_col_type      binary_integer;
  v_col_name      varchar2(100);
  v_col_name_clob varchar2(100);
  v_CLOB          CLOB;
  v_var           varchar2(4000);
  v_rec_num       number;
  v_len           number;
  v_curr_t_name   varchar2(4000) := '*#*#*';
  v_t_name        varchar2(4000);
  v_parent_id_1   number;
   
BEGIN
    -- Switch from native dynamic SQL to DBMS_SQL
    v_curid := DBMS_SQL.TO_CURSOR_NUMBER (p_cv);

    -- Define columns
    DBMS_SQL.DESCRIBE_COLUMNS3(v_curid, v_colcnt, v_desctab);
    FOR i IN 1 .. v_colcnt LOOP
        v_col_type := v_desctab(i).col_type;
        v_col_name := v_desctab(i).col_name;
        
        IF v_col_type = DBMS_SQL.Varchar2_Type THEN
           DBMS_SQL.DEFINE_COLUMN(v_curid, i,  v_var, 4000);
        elsif v_col_type = DBMS_SQL.Number_Type then 
           DBMS_SQL.DEFINE_COLUMN(v_curid, i,  v_rec_num  );
        ELSIF v_desctab(i).col_type = DBMS_SQL.Clob_Type THEN
           DBMS_SQL.DEFINE_COLUMN(v_curid, i, v_clob      );
        ELSE
           DBMS_SQL.DEFINE_COLUMN(v_curid, i, v_var, 4000);
        END IF;
    END LOOP;

    v_rec_num := 0;

  -- Fetch Rows
    WHILE DBMS_SQL.FETCH_ROWS(v_curid) > 0 LOOP
        v_ac_rec := null; -- clear record
        v_rec_num := v_rec_num + 1;

        v_t_name  := getColVal (v_curid, 't_name', v_desctab,  v_colcnt);

        if (v_t_name != v_curr_t_name) then
           v_parent_id_1 := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_t_name, 3, true, p_help_text);
           v_curr_t_name := v_t_name;
        end if;

        v_ac_rec.result_id    := p_result_id;
        v_ac_rec.parent_id    := v_parent_id_1;

        v_ac_rec.param_name   := getColVal (v_curid, 'u_name',    v_desctab,  v_colcnt);
        v_ac_rec.param_val_1  := getColVal (v_curid, 'u_value_1', v_desctab,  v_colcnt);
        v_ac_rec.param_val_2  := getColVal (v_curid, 'u_value_2', v_desctab,  v_colcnt);
        v_ac_rec.sort         := p_sort;
        v_ac_rec.help_text    := getColVal (v_curid, 'src_view_name', v_desctab,  v_colcnt);
        v_ac_rec.is_src       := 'N';
        
        -- if coulumn name ends with _HS then real values are in CLOBS 
        -- field names end with _sr_1 and _sr_2
        -- i.e column name = TEMPLATE_HS values are in
        -- TEMPLATE_SR_1 and TEMPLATE_SR_2
        if (v_ac_rec.param_name like '%\_HS' escape '\') then
         v_len                := length(v_ac_rec.param_name);  
         v_ac_rec.param_name  := substr(v_ac_rec.param_name, 1, v_len - 3);
         
         v_col_name_clob      := v_ac_rec.param_name||'_sr';
         v_ac_rec.src_val_1   := getColValClob(v_curid, v_col_name_clob||'_1', v_desctab,  v_colcnt);
         v_ac_rec.src_val_2   := getColValClob(v_curid, v_col_name_clob||'_2', v_desctab,  v_colcnt);
         v_ac_rec.is_src      := 'Y';
         v_ac_rec.param_val_1 := substr(v_ac_rec.src_val_1,1,4000);
         v_ac_rec.param_val_2 := substr(v_ac_rec.src_val_2,1,4000);
        end if;
        v_ac_rec.help_text    := v_ac_rec.help_text||'.'|| v_ac_rec.param_name; 

        insert into ac_results
          (result_id,     parent_id,    param_name,   
           param_val_1,   param_val_2,        sort,
           help_text,      
           src_val_1,     src_val_2,  is_branch, is_src)
         values
          (p_result_id,   v_parent_id_1, v_ac_rec.param_name,  
           v_ac_rec.param_val_1,v_ac_rec.param_val_2, 1,
           v_ac_rec.help_text, 
           v_ac_rec.src_val_1,  v_ac_rec.src_val_2,  'N',       
          v_ac_rec.is_src);

    END LOOP;
 
    DBMS_SQL.CLOSE_CURSOR(v_curid);
END;

end ac_utils;
/

